package com.xinchuang.service.impl;

import cn.hutool.core.bean.BeanUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.fastjson.JSON;
import com.xinchuang.comment.KeyWord;
import com.xinchuang.comment.R;
import com.xinchuang.domain.type.NormalOutQuotationaSubEnum;
import com.xinchuang.domain.vo.out.*;
import com.xinchuang.domain.vo.out.res.NormalOutQuotationDetailResVo;
import com.xinchuang.domain.vo.out.res.NormalOutQuotationResVo;
import com.xinchuang.domain.vo.out.res.NormalOutQuotationaSubDetailResVo;
import com.xinchuang.service.ExportNormalOutQuotationService;
import com.xinchuang.service.IMoldSelectionService;
import com.xinchuang.utils.BigdecimalUtil;
import com.xinchuang.utils.DateUtils;
import com.xinchuang.utils.EasyExcelUtil;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.util.IOUtils;
import org.apache.poi.util.Units;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * @Description:导出对外报价
 * @Author: thh
 * @Date: 2023/10/8 11:07
 */
@Slf4j
@Service
@RequiredArgsConstructor
public class ExportNormalOutQuotationServiceImpl implements ExportNormalOutQuotationService {

    private final KeyWord keyWord;

    @Autowired
    private IMoldSelectionService iMoldSelectionService;

    /**
     * 通用外部报价单导出
     *
     * @param data
     */
    @Override
    public String exportNormalOutQuotation(String data) {
        String rootUrl = keyWord.getFileUrl();
        String resultPath = "";
        try {
            R r = JSON.parseObject(data, R.class);
            NormalOutQuotationResVo normalOutQuotationResVo = JSON.parseObject(JSON.toJSONString(r.getData()), NormalOutQuotationResVo.class);
            String moldSelectionId = normalOutQuotationResVo.getMoldSelectionId();
            String projectName = iMoldSelectionService.getProjectName(moldSelectionId);
            resultPath = "file" + File.separator + projectName + "项目-通用外部报价单-" + DateUtils.dateTimeNow("yyyyMMdd-SSS") + ".xlsx";
            String filePath = rootUrl + File.separator + resultPath;
            //处理特殊字段
            normalOutQuotationResVo.setAddress(matchStr(normalOutQuotationResVo.getAddress()));
            for (int i = 0; i < normalOutQuotationResVo.getDataList().size(); i++) {
                NormalOutQuotationDetailResVo normalOutQuotationDetailResVo = normalOutQuotationResVo.getDataList().get(i);
                normalOutQuotationDetailResVo.setIndex(i + 1);
                if (StringUtils.isNotBlank(normalOutQuotationDetailResVo.getImageOfProduct())) {
                    normalOutQuotationDetailResVo.setImageFile(new File(rootUrl + File.separator + normalOutQuotationDetailResVo.getImageOfProduct()));
                }
            }

            Map<String, Object> dataMap = BeanUtil.beanToMap(normalOutQuotationResVo, false, false);
            double skinGrainFeeSum = normalOutQuotationResVo.getDataList().stream().mapToDouble(x -> BigDecimal.valueOf(x.getSkinGrainFee()).doubleValue()).sum();
            double mouldFeeSum = normalOutQuotationResVo.getDataList().stream().mapToDouble(x -> BigDecimal.valueOf(x.getMouldFee()).doubleValue()).sum();
            double totalPriceWithoutTaxIncludesSkinGrainSum = normalOutQuotationResVo.getDataList().stream().mapToDouble(x ->
                    BigDecimal.valueOf(x.getTotalPriceWithoutTaxIncludesSkinGrain()).doubleValue()).sum();
            dataMap.put("skinGrainFeeSum", BigdecimalUtil.doubleTo2DecimalString(skinGrainFeeSum));
            dataMap.put("mouldFeeSum", BigdecimalUtil.doubleTo2DecimalString(mouldFeeSum));
            dataMap.put("totalPriceWithoutTaxIncludesSkinGrainSum", BigdecimalUtil.doubleTo2DecimalString(totalPriceWithoutTaxIncludesSkinGrainSum));
            String date = "";
            List<String> sheetNameList = new ArrayList<>(Arrays.asList("总表"));
            if (Objects.nonNull(normalOutQuotationResVo.getDataList()) && !normalOutQuotationResVo.getDataList().isEmpty()) {
                if (Objects.nonNull(normalOutQuotationResVo.getDataList().get(0).getDetailData())) {
                    date = normalOutQuotationResVo.getDataList().get(0).getDetailData().getDate();
                }
                //多sheet页逻辑
                for (int i = 0; i < normalOutQuotationResVo.getDataList().size(); i++) {
                    sheetNameList.add(String.valueOf(i + 1));
                }
            }
            dataMap.put("date", date);
            try (ExcelWriter excelWriter = EasyExcel.write(filePath)
                    .inMemory(Boolean.TRUE)
                    .registerWriteHandler(EasyExcelUtil.getStyleStrategy())
                    .withTemplate(getClass().getClassLoader().getResourceAsStream("templates/common_quotation_template.xlsx"))
                    .build()) {
                FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
                //总表
                WriteSheet writeSheet = EasyExcel.writerSheet("总表").build();
                excelWriter.fill(normalOutQuotationResVo.getDataList(), fillConfig, writeSheet);
                excelWriter.fill(dataMap, writeSheet);
                excelWriter.finish();
            }

            //在内存中创建一个Excel文件对象
            FileInputStream fileInputStream = new FileInputStream(filePath);
            XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);

            sheetNameList = sheetNameList.stream().filter(sheetName -> !sheetName.equals("总表")).collect(Collectors.toList());
            for (String sheetName : sheetNameList) {
                XSSFSheet sheet = workbook.createSheet(sheetName);
                NormalOutQuotationDetailResVo normalOutQuotationDetailResVo = normalOutQuotationResVo.getDataList().get(Integer.valueOf(sheetName) - 1);
                NormalOutQuotationaSubDetailResVo normalOutQuotationaSubDetailResVo = normalOutQuotationDetailResVo.getDetailData();
                //创建Sheet页
                mergedRegion(sheet, 0, 4, 0, 4, "成都航天模塑股份有限公司成都模具分公司");
                mergedRegion(sheet, 0, 2, 5, 15, "模具供应商报价单");
                mergedRegion(sheet, 3, 4, 5, 15, "Mold Quotation Form");
                mergedRegion(sheet, 0, 2, 16, 16, "联系人/Attention:");
                mergedRegion(sheet, 3, 4, 16, 16, "电话/TEL:");
                mergedRegion(sheet, 0, 2, 17, 17, normalOutQuotationaSubDetailResVo.getContacts());
                mergedRegion(sheet, 3, 4, 17, 17, normalOutQuotationaSubDetailResVo.getPhone());

                mergedRegion(sheet, 5, 5, 0, 4, "模具名称/Mold Specification");
                mergedRegion(sheet, 6, 6, 0, 4, normalOutQuotationDetailResVo.getNameOfProduct());
                mergedRegion(sheet, 7, 7, 0, 4, "产品大小/Part Size (mm)");
                mergedRegion(sheet, 8, 8, 0, 4, Arrays.asList(normalOutQuotationaSubDetailResVo.getLengthOfProduct(),
                                normalOutQuotationaSubDetailResVo.getWidthOfProduct(),
                                normalOutQuotationaSubDetailResVo.getHeightOfProduct())
                        .stream().map(Object::toString).collect(Collectors.joining("*")));
                mergedRegion(sheet, 9, 9, 0, 4, "模具名称/外形尺寸/Mold Size (mm)");
                mergedRegion(sheet, 10, 10, 0, 4, Arrays.asList(normalOutQuotationaSubDetailResVo.getLengthOfShape(),
                                normalOutQuotationaSubDetailResVo.getWidthOfShape(),
                                normalOutQuotationaSubDetailResVo.getHeightOfShape())
                        .stream().map(Object::toString).collect(Collectors.joining("*")));

                mergedRegion(sheet, 5, 5, 5, 6, "型腔数量/Cav.");
                mergedRegion(sheet, 6, 6, 5, 6, normalOutQuotationDetailResVo.getCavityCount());
                mergedRegion(sheet, 7, 7, 5, 6, "产品重量/Part Weight(g)");
                mergedRegion(sheet, 8, 8, 5, 6, String.valueOf(normalOutQuotationaSubDetailResVo.getWeightOfProduct()));
                mergedRegion(sheet, 9, 9, 5, 6, "模具重量/Mold Weight(Kg)");
                mergedRegion(sheet, 10, 10, 5, 6, String.valueOf(normalOutQuotationaSubDetailResVo.getMoldWeight()));

                cellImage(workbook, sheet, rootUrl + File.separator + normalOutQuotationDetailResVo.getImageOfProduct(), 7, 5, 14, 10);

                mergedRegion(sheet, 5, 5, 15, 16, "产品零件号/Part No.");
                mergedRegion(sheet, 6, 6, 15, 16, normalOutQuotationaSubDetailResVo.getCodeOfProduct());
                mergedRegion(sheet, 7, 7, 15, 16, "数据文件名/Drawing No.");
                mergedRegion(sheet, 8, 8, 15, 16, normalOutQuotationaSubDetailResVo.getDrawingNo());
                mergedRegion(sheet, 9, 9, 15, 16, "模具寿命/Longevity");
                mergedRegion(sheet, 10, 10, 15, 16, normalOutQuotationaSubDetailResVo.getLongevity());

                getRow(sheet, 5).createCell(17).setCellValue("首次试模时间/T1 Time");
                getRow(sheet, 6).createCell(17).setCellValue(normalOutQuotationaSubDetailResVo.getT0Time());
                getRow(sheet, 7).createCell(17).setCellValue("最终交付时间/Lead Time");
                getRow(sheet, 8).createCell(17).setCellValue(normalOutQuotationaSubDetailResVo.getLeadTime());
                getRow(sheet, 9).createCell(17).setCellValue("设备吨位/Press(Ton)");
                getRow(sheet, 10).createCell(17).setCellValue(normalOutQuotationaSubDetailResVo.getPressTon());

                mergedRegion(sheet, 11, 11, 0, 17, "");

                MachiningMaterialCost machiningMaterialCost = normalOutQuotationaSubDetailResVo.getMachiningMaterialCost();
                List<MachiningMaterialCost.DataList> machiningMaterialCostDataList = machiningMaterialCost.getDataList();
                mergedRegion(sheet, 12, 12 + machiningMaterialCostDataList.size(), 0, 1, NormalOutQuotationaSubEnum.MACHINING_MATERIAL_COST.getCn());
                mergedRegion(sheet, 13, 12 + machiningMaterialCostDataList.size(), 17, 17, String.valueOf(machiningMaterialCost.getSubTotal()));
                mergedRegion(sheet, 12, 12, 2, 5, "材料名称/Material");
                mergedRegion(sheet, 12, 12, 6, 7, "材料牌号/Specification");
                mergedRegion(sheet, 12, 12, 8, 12, "尺寸/Size(mm×mm×mm)");
                mergedRegion(sheet, 12, 12, 13, 14, "重量/Weight(Kg)");
                getRow(sheet, 12).createCell(15).setCellValue("单价(元)/Unit Price(RMB)");
                getRow(sheet, 12).createCell(16).setCellValue("金额(元)/Price(RMB)");
                getRow(sheet, 12).createCell(17).setCellValue("小计(元)/Subtotal(RMB)");

                //当前row
                int row = 13;
                //特殊背景颜色处理
                List<Integer> colorList = new ArrayList<>();
                colorList.add(11);
                for (int i = 0; i < machiningMaterialCostDataList.size(); i++) {
                    mergedRegion(sheet, row, row, 2, 5, machiningMaterialCostDataList.get(i).getItemName());
                    mergedRegion(sheet, row, row, 6, 7, machiningMaterialCostDataList.get(i).getSpecification());
                    getRow(sheet, row).createCell(8).setCellValue(machiningMaterialCostDataList.get(i).getLength());
                    getRow(sheet, row).createCell(9).setCellValue("X");
                    getRow(sheet, row).createCell(10).setCellValue(machiningMaterialCostDataList.get(i).getWidth());
                    getRow(sheet, row).createCell(11).setCellValue("X");
                    getRow(sheet, row).createCell(12).setCellValue(machiningMaterialCostDataList.get(i).getHeight());
                    mergedRegion(sheet, row, row, 13, 14, String.valueOf(machiningMaterialCostDataList.get(i).getWeight()));
                    getRow(sheet, row).createCell(15).setCellValue(machiningMaterialCostDataList.get(i).getPrice());
                    getRow(sheet, row).createCell(16).setCellValue(machiningMaterialCostDataList.get(i).getTotalPrice());
                    row++;
                }

                mergedRegion(sheet, row, row, 0, 17, "");
                colorList.add(row);
                row++;

                HeatTreatmentCost heatTreatmentCost = normalOutQuotationaSubDetailResVo.getHeatTreatmentCost();
                List<HeatTreatmentCost.DataList> heatTreatmentCostDataList = heatTreatmentCost.getDataList();
                mergedRegion(sheet, row, row + heatTreatmentCostDataList.size(), 0, 1, NormalOutQuotationaSubEnum.HEAT_TREATMENT.getCn());
                mergedRegion(sheet, row + 1, row + heatTreatmentCostDataList.size(), 17, 17, String.valueOf(heatTreatmentCost.getSubTotal()));
                mergedRegion(sheet, row, row, 2, 5, "热处理名称/Item");
                mergedRegion(sheet, row, row, 6, 7, "重量/Weight(Kg)");
                mergedRegion(sheet, row, row, 8, 14, "单价(元)/Unit Price(RMB)");
                mergedRegion(sheet, row, row, 15, 16, "金额(元)/Price(RMB)");
                getRow(sheet, row).createCell(17).setCellValue("小计(元)/Subtotal(RMB)");
                row++;
                for (int i = 0; i < heatTreatmentCostDataList.size(); i++) {
                    mergedRegion(sheet, row, row, 2, 5, heatTreatmentCostDataList.get(i).getItemName());
                    mergedRegion(sheet, row, row, 6, 7, String.valueOf(heatTreatmentCostDataList.get(i).getWeight()));
                    mergedRegion(sheet, row, row, 8, 14, String.valueOf(heatTreatmentCostDataList.get(i).getPrice()));
                    mergedRegion(sheet, row, row, 15, 16, String.valueOf(heatTreatmentCostDataList.get(i).getTotalPrice()));
                    row++;
                }

                mergedRegion(sheet, row, row, 0, 17, "");
                colorList.add(row);
                row++;

                AssemlyMaterialCost assemlyMaterialCost = normalOutQuotationaSubDetailResVo.getAssemlyMaterialCost();
                List<AssemlyMaterialCost.DataList> assemlyMaterialCostDataList = assemlyMaterialCost.getDataList();
                mergedRegion(sheet, row, row + assemlyMaterialCostDataList.size(), 0, 1, NormalOutQuotationaSubEnum.ASSEMLY_MATERIAL_COST.getCn());
                mergedRegion(sheet, row + 1, row + assemlyMaterialCostDataList.size(), 17, 17, String.valueOf(assemlyMaterialCost.getSubTotal()));
                mergedRegion(sheet, row, row, 2, 5, "装配件/Item");
                mergedRegion(sheet, row, row, 6, 7, "规格型号/Specification");
                mergedRegion(sheet, row, row, 8, 12, "品牌/Supplier");
                mergedRegion(sheet, row, row, 13, 14, "数量/Number");
                getRow(sheet, row).createCell(15).setCellValue("单价(元)/Unit Price(RMB)");
                getRow(sheet, row).createCell(16).setCellValue("金额(元)/Price(RMB)");
                getRow(sheet, row).createCell(17).setCellValue("小计(元)/Subtotal(RMB)");
                row++;
                for (int i = 0; i < assemlyMaterialCostDataList.size(); i++) {
                    mergedRegion(sheet, row, row, 2, 5, assemlyMaterialCostDataList.get(i).getItemName());
                    mergedRegion(sheet, row, row, 6, 7, String.valueOf(assemlyMaterialCostDataList.get(i).getSpecification()));
                    mergedRegion(sheet, row, row, 8, 12, String.valueOf(assemlyMaterialCostDataList.get(i).getSupplier()));
                    mergedRegion(sheet, row, row, 13, 14, String.valueOf(assemlyMaterialCostDataList.get(i).getCount()));
                    getRow(sheet, row).createCell(15).setCellValue(assemlyMaterialCostDataList.get(i).getPrice());
                    getRow(sheet, row).createCell(16).setCellValue(assemlyMaterialCostDataList.get(i).getTotalPrice());
                    row++;
                }

                mergedRegion(sheet, row, row, 0, 17, "");
                colorList.add(row);
                row++;

                DesignFee designFee = normalOutQuotationaSubDetailResVo.getDesignFee();
                List<DesignFee.DataList> designFeeDataList = designFee.getDataList();
                mergedRegion(sheet, row, row + designFeeDataList.size(), 0, 1, NormalOutQuotationaSubEnum.DESIGN_FEE.getCn());
                mergedRegion(sheet, row + 1, row + designFeeDataList.size(), 17, 17, String.valueOf(designFee.getSubTotal()));
                mergedRegion(sheet, row, row, 2, 5, "装配件/Item");
                mergedRegion(sheet, row, row, 6, 7, "工时(小时)/Hour");
                mergedRegion(sheet, row, row, 8, 14, "单价(元)/Unit Price(RMB)");
                mergedRegion(sheet, row, row, 15, 16, "金额(元)/Price(RMB)");
                getRow(sheet, row).createCell(17).setCellValue("小计(元)/Subtotal(RMB)");
                row++;
                for (int i = 0; i < designFeeDataList.size(); i++) {
                    mergedRegion(sheet, row, row, 2, 5, designFeeDataList.get(i).getItemName());
                    mergedRegion(sheet, row, row, 6, 7, String.valueOf(designFeeDataList.get(i).getHour()));
                    mergedRegion(sheet, row, row, 8, 14, String.valueOf(designFeeDataList.get(i).getPrice()));
                    mergedRegion(sheet, row, row, 15, 16, String.valueOf(designFeeDataList.get(i).getTotalPrice()));
                    row++;
                }

                mergedRegion(sheet, row, row, 0, 17, "");
                colorList.add(row);
                row++;

                ManufacturingCost manufacturingCost = normalOutQuotationaSubDetailResVo.getManufacturingCost();
                List<ManufacturingCost.DataList> manufacturingCostDataList = manufacturingCost.getDataList();
                mergedRegion(sheet, row, row + manufacturingCostDataList.size(), 0, 1, NormalOutQuotationaSubEnum.MANUFACTURING_COST.getCn());
                mergedRegion(sheet, row + 1, row + manufacturingCostDataList.size(), 17, 17, String.valueOf(manufacturingCost.getSubTotal()));
                mergedRegion(sheet, row, row, 2, 5, "名称/Item");
                mergedRegion(sheet, row, row, 6, 7, "工时(小时)/Hour");
                mergedRegion(sheet, row, row, 8, 14, "单价(元)/Unit Price(RMB)");
                mergedRegion(sheet, row, row, 15, 16, "金额(元)/Price(RMB)");
                getRow(sheet, row).createCell(17).setCellValue("小计(元)/Subtotal(RMB)");
                row++;
                for (int i = 0; i < manufacturingCostDataList.size(); i++) {
                    mergedRegion(sheet, row, row, 2, 5, manufacturingCostDataList.get(i).getItemName());
                    mergedRegion(sheet, row, row, 6, 7, String.valueOf(manufacturingCostDataList.get(i).getHour()));
                    mergedRegion(sheet, row, row, 8, 14, String.valueOf(manufacturingCostDataList.get(i).getPrice()));
                    mergedRegion(sheet, row, row, 15, 16, String.valueOf(manufacturingCostDataList.get(i).getTotalPrice()));
                    row++;
                }

                mergedRegion(sheet, row, row, 0, 17, "");
                colorList.add(row);
                row++;

                Cmmmeasure cmmmeasure = normalOutQuotationaSubDetailResVo.getCmmmeasure();
                List<Cmmmeasure.DataList> cmmmeasureDataList = cmmmeasure.getDataList();
                mergedRegion(sheet, row, row + cmmmeasureDataList.size(), 0, 1, NormalOutQuotationaSubEnum.CMM_MEASURE.getCn());
                mergedRegion(sheet, row + 1, row + cmmmeasureDataList.size(), 17, 17, String.valueOf(cmmmeasure.getSubTotal()));
                mergedRegion(sheet, row, row, 2, 5, "名称/Item");
                mergedRegion(sheet, row, row, 6, 7, "工时(小时)/Hour");
                mergedRegion(sheet, row, row, 8, 14, "单价(元)/Unit Price(RMB)");
                mergedRegion(sheet, row, row, 15, 16, "金额(元)/Price(RMB)");
                getRow(sheet, row).createCell(17).setCellValue("小计(元)/Subtotal(RMB)");
                row++;
                for (int i = 0; i < cmmmeasureDataList.size(); i++) {
                    mergedRegion(sheet, row, row, 2, 5, cmmmeasureDataList.get(i).getItemName());
                    mergedRegion(sheet, row, row, 6, 7, String.valueOf(cmmmeasureDataList.get(i).getHour()));
                    mergedRegion(sheet, row, row, 8, 14, String.valueOf(cmmmeasureDataList.get(i).getPrice()));
                    mergedRegion(sheet, row, row, 15, 16, String.valueOf(cmmmeasureDataList.get(i).getTotalPrice()));
                    row++;
                }

                mergedRegion(sheet, row, row, 0, 17, "");
                colorList.add(row);
                row++;

                OtherFee otherFee = normalOutQuotationaSubDetailResVo.getOtherFee();
                List<OtherFee.DataList> otherFeeDataList = otherFee.getDataList();
                mergedRegion(sheet, row, row + otherFeeDataList.size(), 0, 1, NormalOutQuotationaSubEnum.OTHER_FEE.getCn());
                mergedRegion(sheet, row + 1, row + otherFeeDataList.size(), 17, 17, String.valueOf(otherFee.getSubTotal()));
                mergedRegion(sheet, row, row, 2, 5, "名称/Item");
                mergedRegion(sheet, row, row, 6, 14, "费用计算说明/Descripition");
                mergedRegion(sheet, row, row, 15, 16, "金额(元)/Price(RMB)");
                getRow(sheet, row).createCell(17).setCellValue("小计(元)/Subtotal(RMB)");
                row++;
                for (int i = 0; i < otherFeeDataList.size(); i++) {
                    mergedRegion(sheet, row, row, 2, 5, otherFeeDataList.get(i).getItemName());
                    mergedRegion(sheet, row, row, 6, 14, String.valueOf(otherFeeDataList.get(i).getFeeDesc()));
                    mergedRegion(sheet, row, row, 15, 16, String.valueOf(otherFeeDataList.get(i).getTotalPrice()));
                    row++;
                }

                mergedRegion(sheet, row, row, 0, 17, "");
                colorList.add(row);
                row++;

                List<Integer> fontList = new ArrayList<>();
                fontList.add(row);
                mergedRegion(sheet, row, row + 3, 0, 5, "皮纹价格(元) \nRMB");
                mergedRegion(sheet, row, row + 3, 6, 17, "￥" + BigdecimalUtil.doubleTo2DecimalString(normalOutQuotationDetailResVo.getSkinGrainFee()));
                row += 4;

                fontList.add(row);
                mergedRegion(sheet, row, row + 3, 0, 5, "模具总价(元) \n(RMB)");
                mergedRegion(sheet, row, row + 3, 6, 17, "￥" + BigdecimalUtil.doubleTo2DecimalString(normalOutQuotationaSubDetailResVo.getMouldFee()));
                row += 4;

                fontList.add(row);
                mergedRegion(sheet, row, row + 3, 0, 5, "总价(元) \nRMB");
                mergedRegion(sheet, row, row + 3, 6, 17, "￥" + BigdecimalUtil.doubleTo2DecimalString(normalOutQuotationaSubDetailResVo.getTotalPrice()));
                row += 4;

                mergedRegion(sheet, row, row, 0, 17, "");
                row++;

                List<Integer> fontList2 = new ArrayList<>();
                mergedRegion(sheet, row, row, 0, 3, "供应商/Supplier:");
                mergedRegion(sheet, row, row, 4, 12, normalOutQuotationaSubDetailResVo.getSupplier());
                mergedRegion(sheet, row, row, 13, 15, "签字/Signature:");
                mergedRegion(sheet, row, row, 16, 17, normalOutQuotationaSubDetailResVo.getSignature());
                fontList2.add(row);
                row++;

                mergedRegion(sheet, row, row, 0, 17, "");
                row++;

                mergedRegion(sheet, row, row, 0, 3, "盖章/Stamp:");
                mergedRegion(sheet, row, row, 4, 12, normalOutQuotationaSubDetailResVo.getStamp());
                mergedRegion(sheet, row, row, 13, 15, "日期/Date:");
                mergedRegion(sheet, row, row, 16, 17, normalOutQuotationaSubDetailResVo.getDate());
                fontList2.add(row);

                // 设置整个表格的样式
                sheet.forEach(sheetRow -> {
                    sheetRow.forEach(cell -> {
                        cell.setCellStyle(createCellStyle(workbook));
                    });
                    // 设置行高自适应
                    sheetRow.setHeight((short) -1);
                });

                //特殊样式
                getRow(sheet, 0).getCell(0).setCellStyle(specialCellStyle(workbook, false, 18));
                getRow(sheet, 0).getCell(5).setCellStyle(specialCellStyle(workbook, false, 18));
                getRow(sheet, 3).getCell(5).setCellStyle(specialCellStyle(workbook, false, 18));
                colorList.forEach(index -> {
                    getRow(sheet, index).getCell(0).setCellStyle(specialCellStyle(workbook, true, 18));
                });
                fontList.forEach(index -> {
                    getRow(sheet, index).getCell(0).setCellStyle(specialCellStyle(workbook, false, 12));
                    getRow(sheet, index).getCell(6).setCellStyle(specialCellStyle(workbook, false, 12));
                });
                fontList2.forEach(index -> {
                    getRow(sheet, index).getCell(0).setCellStyle(specialCellStyle(workbook, false, 16));
                    getRow(sheet, index).getCell(4).setCellStyle(specialCellStyle(workbook, false, 16));
                    getRow(sheet, index).getCell(13).setCellStyle(specialCellStyle(workbook, false, 16));
                    getRow(sheet, index).getCell(17).setCellStyle(specialCellStyle(workbook, false, 16));
                });

                for (int i = 0; i < sheet.getRow(0).getLastCellNum(); i++) {
                    sheet.autoSizeColumn(i);
                }
            }

            FileOutputStream out = new FileOutputStream(filePath);
            //通过输出流将内存中的Excel文件写入到磁盘上
            workbook.write(out);
            //关闭资源
            out.flush();
            out.close();
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        log.info("通用外部报价单导出成功");

        return resultPath;
    }


    /**
     * 合并单元格
     *
     * @param sheet
     * @param firstRow
     * @param lastRow
     * @param firstCol
     * @param lastCol
     * @param cellValue
     */
    private void mergedRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol, String cellValue) {
        // 合并单元格
        CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.addMergedRegion(region);
        //设置样式
        regionStyle(sheet, region);
        //赋值
        getRow(sheet, firstRow).createCell(firstCol).setCellValue(cellValue);
    }

    /**
     * 获取行
     *
     * @param sheet
     * @param firstRow
     * @return
     */
    private Row getRow(Sheet sheet, int firstRow) {
        return sheet.getRow(firstRow) == null ? sheet.createRow(firstRow) : sheet.getRow(firstRow);
    }

    /**
     * 特殊样式
     *
     * @param workbook
     * @return
     */
    private XSSFCellStyle specialCellStyle(XSSFWorkbook workbook, boolean isForeground, int fontSize) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setWrapText(true);
        //加边框
        cellStyle.setBorderBottom(BorderStyle.THIN);//下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框
        if (isForeground) {
            //自定义颜色对象
            cellStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(253, 233, 217)));
        } else {
            cellStyle.setFillForegroundColor((short) 9);
        }

        XSSFFont font = workbook.createFont();
        //设置字体名称
        font.setFontName("宋体");
        font.setBold(true);
        font.setFontHeightInPoints((short) fontSize);//设置字号
        //设置字体颜色
        font.setColor(IndexedColors.BLUE.index);
        cellStyle.setFont(font);

        return cellStyle;
    }

    /**
     * 设置单元格样式
     */
    private XSSFCellStyle createCellStyle(XSSFWorkbook workbook) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //加边框
        cellStyle.setBorderBottom(BorderStyle.THIN);//下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框
        cellStyle.setFillForegroundColor((short) 9);
        cellStyle.setWrapText(true);
        XSSFFont font = workbook.createFont();
        //设置字体名称
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 12);//设置字号
        //设置字体颜色
        font.setColor(IndexedColors.BLACK1.index);
        cellStyle.setFont(font);

        return cellStyle;
    }

    /**
     * 设置区域样式
     *
     * @param sheet
     * @param cellAddresses
     */
    private void regionStyle(Sheet sheet, CellRangeAddress cellAddresses) {
        // 设置合并区域的边框样式
        RegionUtil.setBorderTop(BorderStyle.THIN, cellAddresses, sheet);
        RegionUtil.setBorderBottom(BorderStyle.THIN, cellAddresses, sheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, cellAddresses, sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, cellAddresses, sheet);
    }

    //Excel单元格插入图片
    public static void cellImage(Workbook wb, Sheet sheet, String imageUrl, int firstCol, int firstRow, int lastCol, int lastRow) {
        try {
            // 读取图片
            InputStream is = new FileInputStream(imageUrl);
            byte[] bytes = IOUtils.toByteArray(is);
            int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
            is.close();

            CreationHelper helper = wb.getCreationHelper();
            // 创建合并单元格
            CellRangeAddress mergedRegion = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
            sheet.addMergedRegion(mergedRegion);

            // 创建图像
            Drawing drawing = sheet.createDrawingPatriarch();
            ClientAnchor anchor = helper.createClientAnchor();
            anchor.setCol1(mergedRegion.getFirstColumn());
            anchor.setRow1(mergedRegion.getFirstRow());
            anchor.setCol2(mergedRegion.getLastColumn());
            anchor.setRow2(mergedRegion.getLastRow());
            anchor.setDx1(0);
            anchor.setDy1(0);
            anchor.setDx2(0);
            anchor.setDy2(0);
            Picture pict = drawing.createPicture(anchor, pictureIdx);

            // 计算合并单元格的宽度和高度
            double mergedWidth = 0.0;
            for (int i = mergedRegion.getFirstColumn(); i <= mergedRegion.getLastColumn(); i++) {
                mergedWidth += sheet.getColumnWidth(i) / 256.0 * 7.0;
            }

            double mergedHeight = 0.0;
            for (int i = mergedRegion.getFirstRow(); i <= mergedRegion.getLastRow(); i++) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    mergedHeight += row.getHeightInPoints() / 72 * 96;
                } else {
                    mergedHeight += sheet.getDefaultRowHeightInPoints() / 72 * 96;
                }
            }

            // 获取图像的原始大小
            double imageWidth = pict.getImageDimension().getWidth();
            double imageHeight = pict.getImageDimension().getHeight();

            // 计算图像的新大小以适应合并单元格
            double ratio = Math.min(mergedWidth / imageWidth, mergedHeight / imageHeight);
            int newWidth = (int) (imageWidth * ratio);
            int newHeight = (int) (imageHeight * ratio);

            // 设置图像的新大小
            anchor.setDx2(Units.toEMU(newWidth));
            anchor.setDy2(Units.toEMU(newHeight));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 匹配字符串
     *
     * @param text
     * @return
     */
    private String matchStr(String text) {
        Pattern pattern = Pattern.compile("[a-zA-Z]");
        Matcher matcher = pattern.matcher(text);
        // 查找第一个匹配项
        if (matcher.find()) {
            // 获取第一个英文字符的下标
            int index = matcher.start();
            String firstLine = text.substring(0, index);
            String secondLine = text.substring(index);
            text = firstLine + "\n" + secondLine;
        }
        return text;
    }
}
